import xlrd
import xlwt
from xlutils.copy import copy
from xlwt import XFStyle

import DateTime
import Mongo

qxname = DateTime.DateTime().qxname()
qx = Mongo.Mongo("qx", qxname)  # 读取当月抢修表
count = qx.collCount()  # 读取当月抢修表的个数
jsb = Mongo.Mongo("JSB", "jsb")  # 当去结算表


def yyyy():
    style = XFStyle()  # 赋值style为XFStyle()，初始化样式
    styletop = XFStyle()  # 赋值style为XFStyle()，初始化样式

    font = xlwt.Font()
    font.name = "宋体"
    font.bold = False  # 是否加粗
    font.height = 20 * 11
    style.font = font

    font1 = xlwt.Font()
    font1.name = "宋体"
    font1.bold = True  # 是否加粗
    font1.height = 20 * 12
    styletop.font = font1

    borders = xlwt.Borders()
    borders.top = 1
    borders.bottom = 1
    borders.left = 1
    borders.right = 1
    style.borders = borders

    borders1 = xlwt.Borders()
    borders1.top = 1
    borders1.bottom = 1
    borders1.left = 1
    borders1.right = 0
    styletop.borders = borders1

    alignment = xlwt.Alignment()
    alignment.horz = 0x02
    alignment.vert = 0x01
    alignment.wrap = 1
    style.alignment = alignment

    alignment1 = xlwt.Alignment()
    alignment1.horz = 0x01
    alignment1.vert = 0x01
    alignment1.wrap = 0
    styletop.alignment = alignment1
    year = DateTime.DateTime().MkdirYear()
    month = DateTime.DateTime().MkdirMonth()
    foldname = "D:/" + year + "/" + month + "/总表"  # 文件夹盘所在D盘的位置)
    rb = xlrd.open_workbook('./excel/东方有线2020抢修结算表moban.xls', formatting_info=True)  # 打开weng.xls文件
    wb = copy(rb)  # 利用xlutils.copy下的copy函数复制
    ws = wb.get_sheet(0)  # 打开材料表
    listWl = [{"_id": 158, "unit": 0}]
    for nQX in range(1, count + 1):  # 遍历所有抢修表
        if qx.finddict({"_id": nQX})["jiage"] < 500:
            len1 = len(listWl)
            for nnnnn in range(0, len1):
                if listWl[nnnnn]["_id"] == 158: listWl[nnnnn]["unit"] += 1
        else:
            l = len(qx.finddict({"_id": nQX})["workload"])  # 总价大于50获取workload的长度
            for nWl in range(0, l):
                id = qx.finddict({"_id": nQX})["workload"][nWl]["JSid"]
                ll = len(listWl)
                index = 0
                for nnn in range(0, ll):
                    if listWl[nnn]["_id"] == id:
                        index = nnn
                if index == 0:
                    listWl.append({"_id": id, "unit": qx.finddict({"_id": nQX})["workload"][nWl]["unit"]})
                else:
                    listWl[index]["unit"] += qx.finddict({"_id": nQX})["workload"][nWl]["unit"]
    # print(listWl)
    lenEnd = len(listWl)
    for nEnd in range(0, lenEnd):
        end_id = listWl[nEnd]["_id"]
        jg=jsb.finddict({"_id":end_id})["jiage"]
        endunit = listWl[nEnd]["unit"]
        if end_id in range(1, 33):
            ws.write(2 + end_id, 5, endunit, style)
            ws.write(2 + end_id, 8, round(endunit*jg,2), style)
        elif end_id in range(33, 145):
            ws.write(3 + end_id, 5, endunit, style)
            ws.write(3 + end_id, 8, round(endunit*jg,2), style)
        elif end_id in range(145, 148):
            ws.write(4 + end_id, 5, endunit, style)
            ws.write(4 + end_id, 8, round(endunit*jg,2), style)
        elif end_id in range(148, 156):
            ws.write(5 + end_id, 5, endunit, style)
            ws.write(5 + end_id, 8, round(endunit*jg,2), style)
        elif end_id in range(156, 177):
            ws.write(6 + end_id, 5, endunit, style)
            ws.write(6 + end_id, 8, round(endunit*jg,2), style)
        elif end_id =="GD49":
            ws.write(183, 5, endunit, style)
            ws.write(183, 8, round(endunit*jg,2), style)

    ws.write(1, 0, "项目名称：骨干网抢修" + str(DateTime.DateTime().Yearnum())+"年" + str(DateTime.DateTime().MonthNum()-1) + "月" + "16日至" + str(DateTime.DateTime().Yearnum()) + "年" + str(DateTime.DateTime().MonthNum()) + "月15日", styletop)
    wb.save(foldname + "/"+str(DateTime.DateTime().Yearnum())+"年"+str(DateTime.DateTime().MonthNum())+"月抢修结算表.xls")